"""数据库模型"""

"""需要增加积分商城表  及对应的关系
建立触发器 当订单状态为已完成 用户表中积分得到相对应的增加
积分与消费金额的比例为1：1
"""

from datetime import datetime
from ihome import constants
from ihome import db
from werkzeug.security import generate_password_hash, check_password_hash

class BaseModel(object):
    """模型基类，为每个模型补充创建时间与更新时间"""
    # 记录创建的时间为当前的时间
    create_time = db.Column(db.DateTime, default=datetime.now)
    # 记录更新时间
    update_time = db.Column(db.DateTime, default=datetime.now, onupdate=datetime.now())


class User(BaseModel, db.Model):
    """用户表"""
    __tablename__ = "user_profile"
    id = db.Column(db.INTEGER, primary_key=True, autoincrement=True)  # 用户
    name = db.Column(db.String(32), nullable=False)  # 用户昵称
    password_hash = db.Column(db.String(256), nullable=False)  # 加密的密码
    mobile = db.Column(db.String(11), unique=True, nullable=False)  # 手机号
    real_name = db.Column(db.String(32))  # 真实姓名
    id_card = db.Column(db.String(20))  # 身份证号
    avatar_url = db.Column(db.String(128), default=constants.QINIU_URL_DOMAIN+"test.png")  # 用户头像路径 默认地址
    sex = db.Column(db.String(32), default=1)  # 性别
    sign = db.Column(db.String(128), default=constants.MY_SIGN_DEFAULT)  # 个性签名
    birthday = db.Column(db.DateTime)  # 出生日期
    education = db.Column(db.String(32))  # 教育背景
    works = db.Column(db.String(32))  # 工作信息
    integral = db.Column(db.Float, default=0)  # 积分
    discount = db.Column(db.String(10), default='十')  # 折扣
    houses = db.relationship("House", backref="user")  # 用户发布的房屋
    orders = db.relationship("Order", backref="user")  # 用户下的房屋订单

    def password(self, value):
        self.password_hash = generate_password_hash(value)

    def check_password(self, passwd):
        """
        检验密码的正确性
        :param passwd:  用户登录时填写的原始密码
        :return: 如果正确，返回True， 否则返回False
        """
        return check_password_hash(self.password_hash, passwd)

    def to_dict(self):
        """将对象转换为字典数据"""
        user_dict = {
            "user_id": self.id,
            "name": self.name,
            "mobile": self.mobile,
            "sex": self.sex,
            "education": self.education,
            "works": self.works,
            "sign": self.sign,
            'discount': self.discount,
            "integral": self.integral,
            "avatar": self.avatar_url if self.avatar_url else "",
            "create_time": self.create_time.strftime("%Y-%m-%d %H:%M:%S"),
            # "birthday": self.birthday.strftime("%Y-%m-%d %H:%M:%S") if self.birthday else ""
        }
        return user_dict

    def auth_to_dict(self):
        """将实名信息转换为字典数据"""
        auth_dict = {
            "user_id": self.id,
            "real_name": self.real_name,
            "id_card": self.id_card
        }
        return auth_dict


class UserToStudent(BaseModel, db.Model):
    """学生认证表
     认证的学生用户将 进行全站八折处理
    """
    __tablename__ = "student_auth"
    id = db.Column(db.INTEGER, primary_key=True, autoincrement=True)  # 认证编号
    user_id = db.Column(db.INTEGER, db.ForeignKey("user_profile.id"), nullable=False)  # 用户id
    real_name = db.Column(db.String(32), nullable=False)  # 真实姓名
    school = db.Column(db.String(32), nullable=False)  # 所在学校
    student_voucher_url = db.Column(db.String(256), nullable=False)  # 认证凭证

    def auth_to_dict(self):
        """将学生认证信息转换为字典数据"""
        auth_dict = {
            "id": self.id,
            "real_name": self.real_name,
            "school": self.school,
            "student_voucher_url": self.student_voucher_url
        }
        return auth_dict


class UserAddress(BaseModel, db.Model):
    """
        收货地址表
    """
    __tablename__ = "user_address"
    id = db.Column(db.INTEGER, primary_key=True, autoincrement=True)  # 地址编号
    user_id = db.Column(db.INTEGER, db.ForeignKey("user_profile.id"), nullable=False)  # 用户id
    name = db.Column(db.String(32), nullable=False)  # 收货联系人
    address = db.Column(db.String(128), nullable=False)  # 地址
    phone = db.Column(db.String(11), nullable=False)  # 手机号码

    def to_dict(self):
        """将地址信息转换为字典数据"""
        to_dict = {
            "id": self.id,
            "user_id": self.user_id,
            "name": self.name,
            "address": self.address,
            "phone": self.phone
        }
        return to_dict


class Province(BaseModel, db.Model):
    """省份"""
    __tablename__ = "province_info"  # 表名
    id = db.Column(db.INTEGER, primary_key=True, autoincrement=True)  # 省份编号
    name = db.Column(db.String(32), nullable=False)  # 省份名
    # 使用正向查询到相关联城市
    city = db.relationship("City", backref="city_of_province")

    def to_dict(self):
        """将对象转换为字典"""
        d = {
            "pid": self.id,
            "name": self.name
        }
        return d


class City(BaseModel, db.Model):
    """城市"""
    __tablename__ = "city_info"  # 表名
    id = db.Column(db.INTEGER, primary_key=True, autoincrement=True)  # 城市编号
    name = db.Column(db.String(32), nullable=False)  # 城市名
    p_id = db.Column(db.INTEGER, db.ForeignKey("province_info.id"), nullable=False)
    # 使用正向关联查到对应的区域
    area = db.relationship("Area", backref="area_of_city")

    def to_dict(self):
        """将对象转换为字典"""
        d = {
            "cid": self.id,
            "cname": self.name,
            "p_id": self.p_id
        }
        return d


class Area(BaseModel, db.Model):
    """城区"""

    __tablename__ = "area_info"  # 表名

    id = db.Column(db.INTEGER, primary_key=True, autoincrement=True)  # 区域编号
    name = db.Column(db.String(32), nullable=False)  # 区域名字
    c_id = db.Column(db.INTEGER, db.ForeignKey("city_info.id"), nullable=False)  # 城市编号
    houses = db.relationship("House", backref="area")  # 区域的房屋

    def to_dict(self):
        """将对象转换为字典"""
        d = {
            "aid": self.id,
            "aname": self.name,
            "cid": self.c_id
        }
        return d


# 房屋设施表，建立房屋与设施的多对多关系
house_facility = db.Table(
    "house_facility",
    db.Column("house_id", db.INTEGER, db.ForeignKey("house_info.id"), primary_key=True),  # 房屋编号
    db.Column("facility_id", db.INTEGER, db.ForeignKey("facility_info.id"), primary_key=True),  # 设施编号
)


class HouseCollection(BaseModel,db.Model):
    """房屋收藏表 建立与用户多对多关系"""
    __tablename__ = "house_collection"
    house_id = db.Column("house_id", db.INTEGER, db.ForeignKey("house_info.id"), primary_key=True)  # 房屋编号
    user_id = db.Column("user_id", db.INTEGER, db.ForeignKey("user_profile.id"), primary_key=True)  # 房屋编号

# 房屋收藏表，建立房屋与用户的多对多关系
# house_collection = db.Table(
#     "house_collection",
#     db.Column("house_id", db.INTEGER, db.ForeignKey("house_info.id"), primary_key=True),  # 房屋编号
#     db.Column("user_id", db.INTEGER, db.ForeignKey("user_profile.id"), primary_key=True)  # 用户编号
# )


class House(BaseModel, db.Model):
    """房屋信息"""
    __tablename__ = "house_info"

    id = db.Column(db.INTEGER, primary_key=True, autoincrement=True)  # 房屋编号
    user_id = db.Column(db.INTEGER, db.ForeignKey("user_profile.id"), nullable=False)  # 房屋主人的用户编号
    area_id = db.Column(db.INTEGER, db.ForeignKey("area_info.id"), nullable=False)  # 归属地的区域编号 地区
    title = db.Column(db.String(64), nullable=False)  # 标题
    month_price = db.Column(db.INTEGER, default=0)  # 月租
    address = db.Column(db.String(256), default="")  # 地址
    acreage = db.Column(db.INTEGER, default=0)  # 房屋面积
    house_type = db.Column(db.String(8), default="未知")  # 房屋类型
    lease_type = db.Column(db.String(8), default="未知")  # 出租类型
    position = db.Column(db.String(32), default=1)  # 方位朝向
    floor = db.Column(db.String(32), default="")  # 房屋楼层
    unit = db.Column(db.String(32), default="")  # 房屋单元,如几室几厅
    build_date = db.Column(db.String(32), default="")  # 建造时间
    renovation = db.Column(db.String(8), default="")  # 装修程度
    see_house_date = db.Column(db.DateTime, default="")  # 可看房时间
    house_del = db.Column(db.Text, default="")  # 房屋描述
    index_image_url = db.Column(db.String(256), default="")  # 房屋主图片的路径
    house_status = db.Column(db.String(8), default="空闲")  # 房屋的状态
    order_count = db.Column(db.Integer, default=0)  # 出租完成的该房屋的订单数
    facilities = db.relationship("Facility", secondary=house_facility)  # 房屋的设施
    images = db.relationship("HouseImage")  # 房屋的图片
    orders = db.relationship("Order", backref="houses")  # 房屋的订单
    user_collections = db.relationship("User", secondary="house_collection")  # 收藏房屋的用户

    def to_basic_dict(self):
        """将基本信息转换为字典数据"""
        house_dict = {
            "house_id": self.id,
            "title": self.title,
            "month_price": self.month_price,
            "area_name": self.area.name,
            "index_image_url": self.index_image_url,
            "lease_type": self.lease_type,
            "acreage": self.acreage,
            "renovation": self.renovation,
            "address":self.address,
            "status": self.house_status,
            "create_time": self.create_time.strftime("%Y-%m-%d")
        }
        return house_dict

    def to_dict(self):
        """将详细信息转换为字典数据"""
        house_dict = {
            "house_id": self.id,
            "user_id": self.user_id,
            "title": self.title,
            "area_name": self.area.name,
            "month_price": self.month_price,
            "address": self.address,
            "acreage": self.acreage,
            "house_type": self.house_type,
            "lease_type": self.lease_type,
            "position": self.position,
            "build_date": self.build_date,
            "renovation": self.renovation,
            "status": self.house_status,
            "see_house_date": self.see_house_date.strftime("%Y-%m-%d"),
            "house_del": self.house_del
        }

        # 房屋图片
        img_urls = []
        for image in self.images:
            img_urls.append(image.url)
        house_dict["img_urls"] = img_urls

        # 房屋设施
        facilities = []
        for facility in self.facilities:
            facilities.append(facility.id)
        house_dict["facilities"] = facilities

        # 收藏该房屋的用户Id
        collections = []
        for item in self.user_collections:
            collections.append(item.id)
        house_dict["collections"] = collections

        # 评论信息
        comments = []
        orders = Order.query.filter(Order.house_id == self.id, Order.status == "COMPLETE", Order.comment != None)\
            .order_by(Order.update_time.desc()).limit(constants.HOUSE_DETAIL_COMMENT_DISPLAY_COUNTS)
        for order in orders:
            comment = {
                "comment": order.comment,  # 评论的内容
                "user_name": order.user.name if order.user.name != order.user.mobile else "匿名用户",  # 发表评论的用户
                "ctime": order.update_time.strftime("%Y-%m-%d %H:%M:%S")  # 评价的时间
            }
            comments.append(comment)
        house_dict["comments"] = comments
        return house_dict


class Facility(BaseModel, db.Model):
    """设备信息"""

    __tablename__ = "facility_info"

    id = db.Column(db.INTEGER, primary_key=True, autoincrement=True)  # 设施编号
    name = db.Column(db.String(32))  # 设施名字

    def to_dict(self):
        """将对象转换为字典"""
        facility_dict = {
            "id": self.id,
            "name": self.name
        }
        return facility_dict


class HouseImage(BaseModel, db.Model):
    """房屋图片"""

    __tablename__ = "house_image"

    id = db.Column(db.INTEGER, primary_key=True, autoincrement=True)
    house_id = db.Column(db.INTEGER, db.ForeignKey("house_info.id"), nullable=False)  # 房屋编号
    url = db.Column(db.String(256), nullable=False)  # 图片路径


class Order(BaseModel, db.Model):
    """订单"""

    __tablename__ = "house_order"

    id = db.Column(db.INTEGER, primary_key=True, autoincrement=True)  # 订单编号
    user_id = db.Column(db.INTEGER, db.ForeignKey("user_profile.id"), nullable=False)  # 下订单的用户编号
    house_id = db.Column(db.INTEGER, db.ForeignKey("house_info.id"), nullable=False)  # 订单的房间编号
    name = db.Column(db.String(16), nullable=False)  # 预订的姓名
    phone = db.Column(db.String(16), nullable=False)  # 联系方式
    see_date = db.Column(db.DateTime, nullable=False)  # 看房时间
    lease_month = db.Column(db.INTEGER)  # 出租多少个月月份
    amount = db.Column(db.Float)  # 订单的总金额
    deposit = db.Column(db.Float)  # 订单押金
    trade_no = db.Column(db.String(128))  # 订单交易号码
    contract_image = db.Column(db.String(256), default="")  # 合同图片
    status = db.Column(  # 订单的状态
        db.Enum(
            "WAIT_PAYMENT",  # 待支付
            "CANCELED",  # 已取消
            "PAID",  # 已支付
            "WAIT_COMMENT",  # 待评价
            "COMPLETE",  # 已完成
        ),
        default="WAIT_PAYMENT", index=True)
    comment = db.Column(db.Text)  # 订单的评论信息或者拒单原因

    def to_dict(self):
        """将详细信息转换为字典数据"""
        order_dict = {
            "order_id": self.id,
            "user_id": self.user_id,
            "house_id": self.house_id,
            "name": self.name,
            "phone": self.phone,
            "see_date": self.see_date.strftime("%Y-%m-%d"),
            "amount": self.amount,
            "contract_image": self.contract_image,
            "status": self.status
        }

        # 订单所涉及的房屋基本信息
        house = House.query.filter(House.id == self.house_id) \
            .order_by(House.create_time.desc()).first()
        house_base = house.to_basic_dict()
        print(type(house_base))
        order_dict["house"] = house_base
        # print(self.houses.id)
        return order_dict


class GoodsOrder(BaseModel, db.Model):
    """商品订单"""
    __tablename__ = 'goods_order'
    id = db.Column(db.INTEGER, primary_key=True, autoincrement=True)  # 商品订单id
    user_id = db.Column(db.INTEGER, db.ForeignKey("user_profile.id"), nullable=False)  # 用户id
    goods_id = db.Column(db.INTEGER, db.ForeignKey("integral_goods.id"), nullable=False)  # 商品id
    address_id = db.Column(db.INTEGER, db.ForeignKey("user_address.id"), nullable=False)  # 配送的地址id
    total_price = db.Column(db.INTEGER, nullable=False)  # 花费的积分
    order_status = db.Column(
        db.Enum(
            "UNSENT_GOODS",  # 未发货
            "SENT_GOODS"  # 已发货
        ),
        default="UNSENT_GOODS", index=True)  # 订单状态

    def to_dict(self):
        """将详细信息转换为字典数据"""
        goods_dict = {
            "order_id": self.id,
            "user_id": self.user_id,
            "goods_id": self.goods_id,
            "address_id": self.address_id,
            "total_price": self.total_price,
            "order_status": self.order_status
        }

        # 商品订单所涉及的订单基本信息
        order = IntegralGoods.query.filter(IntegralGoods.id == self.goods_id) \
            .order_by(IntegralGoods.create_time.desc()).first()
        order_base = order.to_base_dict()
        goods_dict["goods"] = order_base

        # 商品订单所涉及的收货地址信息
        address = UserAddress.query.filter(UserAddress.id == self.address_id) \
            .order_by(UserAddress.create_time.desc()).first()
        address_base = address.to_dict()
        goods_dict["address"] = address_base

        return goods_dict


class IntegralGoods(BaseModel, db.Model):
    """积分商品"""
    __tablename__ = 'integral_goods'
    id = db.Column(db.INTEGER, primary_key=True, autoincrement=True)  # 商品id
    name = db.Column(db.String(32), nullable=False)  # 商品名称
    need_integral = db.Column(db.Float, nullable=False)  # 所需积分
    volume = db.Column(db.Integer, nullable=False, default=0)  # 商品销售量
    count = db.Column(db.Integer, nullable=False, default=0)  # 商品数量
    norms = db.Column(db.String(32), nullable=False, default=0)  # 商品规格
    detail = db.Column(db.Text)  # 商品详情
    sort_id = db.Column(db.Integer, db.ForeignKey("goods_sort.id"))  # 商品分类
    index_image = db.Column(db.String(128), nullable=False)  # 商品列表页图片
    images = db.relationship("GoodsImage")  # 商品的图片
    # user_id = db.Column(db.Integer,db.ForeignKey("user_profile.id"),nullable=False)  用户编号

    def to_base_dict(self):
        """将详细信息转换为字典数据"""
        goods_dict = {
            "goods_id": self.id,
            "name": self.name,
            "need_integral": self.need_integral,
            "volume": self.volume,
            "index_image": self.index_image,
            "create_time": self.create_time.strftime("%Y-%m-%d"),
        }
        return goods_dict

    def to_dict(self):
        """将详细信息转换为字典数据"""
        goods_dict = {
            "goods_id": self.id,
            "name": self.name,
            "need_integral": self.need_integral,
            "volume": self.volume,
            "count": self.count,
            "norms": self.norms,
            "detail": self.detail,
            "sort_id": self.sort_id,
            "create_time": self.create_time.strftime("%Y-%m-%d")
        }

        # 商品的所有图片
        img_urls = []
        for image in self.images:
            img_urls.append(image.image_url)
        goods_dict["img_urls"] = img_urls

        return goods_dict

# 多对多关系 积分商品详情
# goods_item = db.Table(
#     "goods_item",
#     db.Column("goods_id", db.INTEGER, db.ForeignKey("integral_goods.id"), primary_key=True),  # 商品id
#     db.Column("order_id", db.INTEGER, db.ForeignKey("goods_order.id"), primary_key=True),  # 订单id
# )


class GoodsImage(BaseModel, db.Model):
    """积分商城商品图片"""
    __tablename__ = "goods_image"
    id = db.Column(db.INTEGER, primary_key=True, autoincrement=True)  # 图片id
    goods_id = db.Column(db.INTEGER, db.ForeignKey('integral_goods.id'), nullable=False)  # 所属的商品id
    image_url = db.Column(db.String(128), nullable=False)  # 图片路径


class GoodsSort(BaseModel, db.Model):
    """积分商品分类"""
    __tablename__ = 'goods_sort'
    id = db.Column(db.INTEGER, primary_key=True, autoincrement=True)  # 分类编号
    name = db.Column(db.String(32), nullable=False)  # 分类名
    goods = db.relationship("IntegralGoods")

    def to_dict(self):
        """将分类信息转换为字典数据"""
        sort_dict = {
            "goods_id": self.id,
            "name": self.name
        }
        return sort_dict
# 当用户订单是已完成的状态下  建立触发器  用户获取到对应金钱的积分  更新用户表中的积分
